ActiveReports 8
Create Top N Reports
Show AllShow All
Hide AllHide All

In a section report, in order to display only the top N number of details on a report, you can manipulate the data pulled by your SQL query.

To set an access data source to pull Top N data

  1. On the design surface, click the DataSource Icon in the detail section band to open the Report Data Source dialog.
  2. On the OLE DB tab of the Report Data Source dialog, next to Connection String, click the Build button.
  3. In the Data Link Properties window that appears, select Microsoft Jet 4.0 OLE DB Provider and click the Next button.
  4. Click the ellipsis (...) button to browse to the NWind database. Click Open once you have selected the appropriate access path.
    Note: The sample NWind.mdb data file is located in: [User Documents folder]\ComponentOne Samples\ActiveReports 8\Data\NWind.mdb
  5. Click OK to close the window and fill in the Connection String field.
  6. Back in Report Data Source dialog, paste the following SQL query in the Query field to fetch Top 10 records from the database.
    SQL Query
    Copy Code
    SELECT TOP 10 Customers.CompanyName, Sum([UnitPrice]*[Quantity]) 
    AS Sales 
    FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) 
    INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID 
    GROUP BY Customers.CompanyName 
    ORDER BY Sum([UnitPrice]*[Quantity]) 
    DESC
    
  7. Click OK to return to the report design surface.

To add controls to display the Top N data

  1. In the Report Explorer, expand the Fields node, then the Bound node.
  2. Drag and drop the following fields onto the detail section and set the properties of each textbox as indicated.
    Field Text Location Miscellaneous
    CompanyName Company Name 0.5, 0
    Sales Sales 5, 0 OutputFormat = Currency
  3. Go to Preview tab, to view the result.

A report with the Top 10 companies' data similar to the following will appear in the preview.

See Also
Support Forum